package org.skydiveapplibrary.data;

import java.io.File;

import org.skydiveapplibrary.repository.SettingsRepository;
import org.skydiveapplibrary.util.FileManager;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DatabaseUpgrade_2_3
{
	private static final String LOG_ENTRY_DIAGRAM_COLUMN = "diagram";
	private static final String SIGNATURE_IMAGE_COLUMN = "image";
	private FileManager fileManager;
	
	private static final String CREATE_TEMP_SIGNATURE_TABLE =
			"create table signature_temp (" +
			"_id integer primary key autoincrement, " +
			"filename text not null, " +
			"md5 text not null, " +
			"license text not null);";
	
	private static final String LOG_ENTRY_SIGNATURE_QUERY =
			"select " +
			"signature._id, " +
			"signature.image, " +
			"signature.license, " +
			"log_entry_signature.log_entry " +
			"from signature " +
			"inner join log_entry_signature on signature._id = log_entry_signature.signature";
	
	private static final String SETTINGS_QUERY =
			"select " +
			"unit_of_measure, " +
			"default_exit_altitude, " +
			"default_deployment_altitude, " +
			"freefall_time, " +
	        "cutaways " +
	        "from settings, " +
	        "logbook_history";
	
	public void doUpgrade(SQLiteDatabase db, Context context)
	{
		fileManager = new FileManager(context);
		
		// add columns
		db.execSQL("ALTER TABLE log_entry ADD COLUMN distance_to_target integer");
		db.execSQL("ALTER TABLE skydive_type ADD COLUMN _default integer not null default 0");
		db.execSQL("ALTER TABLE aircraft ADD COLUMN _default integer not null default 0");
		db.execSQL("ALTER TABLE signature ADD COLUMN filename string not null default 'null'");
		db.execSQL("ALTER TABLE signature ADD COLUMN md5 string not null default 'null'");

		// add tables
		db.execSQL(Database.CREATE_LOG_ENTRY_IMAGE_TABLE);
		db.execSQL(CREATE_TEMP_SIGNATURE_TABLE);
		
		// migrate data
		createLogEntryImages(db);
		createSignatureImages(db);
		
		// just clear old diagram column
		// (dropping requires creating temp table, copying data, deleting old table)
		clearDiagrams(db);
		updateSettings(db, context);
	}
	
	private void createLogEntryImages(SQLiteDatabase db)
	{
		Cursor cursor = null;

		try
		{
			// get all log entries with diagrams
			cursor = db.query(
					Database.LOG_ENTRY_TABLE, 
					new String[] { Database.LOG_ENTRY_ID, LOG_ENTRY_DIAGRAM_COLUMN },
					LOG_ENTRY_DIAGRAM_COLUMN + " is not null",
					null, null, null, null);

			if (cursor.moveToFirst())
			{
				do
				{
					// get diagram data
					byte[] diagramData = Database.getBinaryData(cursor, LOG_ENTRY_DIAGRAM_COLUMN);
					
					// create new log entry image
					ContentValues values = new ContentValues();
					values.put(Database.LOG_ENTRY_IMAGE_LOG_ENTRY, Database.getInteger(cursor, Database.LOG_ENTRY_ID));
					
					File file = fileManager.writeByteArrayToStorageDir(diagramData);
					
					values.put(Database.LOG_ENTRY_IMAGE_FILENAME, file.getName());
					values.put(Database.LOG_ENTRY_IMAGE_IMAGE_TYPE, LogEntryImageType.Diagram.getStringValue());
					values.put(Database.LOG_ENTRY_IMAGE_MD5, file.getName().replace(".jpg", ""));
					
					// do insert
					db.insert(Database.LOG_ENTRY_IMAGE_TABLE, null, values);
				}
				while (cursor.moveToNext());
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
	}
	
	private void clearDiagrams(SQLiteDatabase db)
	{
		// create new log entry image
		ContentValues values = new ContentValues();
		values.putNull(LOG_ENTRY_DIAGRAM_COLUMN);
		db.update(Database.LOG_ENTRY_TABLE, values, "diagram is not null", null);
	}

	private void createSignatureImages(SQLiteDatabase db)
	{
	Cursor cursor = null;

		try
		{
			// get all log entries with diagrams
			cursor = db.rawQuery(LOG_ENTRY_SIGNATURE_QUERY, null);

			if (cursor.moveToFirst())
			{
				do
				{
					// get diagram data
					byte[] signatureData = Database.getBinaryData(cursor, SIGNATURE_IMAGE_COLUMN);

					// create new log entry image
					ContentValues values = new ContentValues();
					
					File file = fileManager.writeByteArrayToStorageDir(signatureData);
					
					values.put(Database.SIGNATURE_FILENAME, file.getName());
					values.put(Database.SIGNATURE_MD5, file.getName().replace(".jpg", ""));
					values.put(Database.SIGNATURE_LICENSE, Database.getString(cursor, Database.SIGNATURE_LICENSE));
					
					// do insert
					Long signatueId = db.insert("signature_temp", null, values);
					
					db.execSQL("UPDATE log_entry_signature SET signature = " + signatueId + 
							" WHERE log_entry = " + Database.getInteger(cursor, "log_entry"));
				}
				while (cursor.moveToNext());
				
				db.execSQL("DROP TABLE signature");
				db.execSQL("ALTER TABLE signature_temp RENAME TO signature");
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
	}
	
	private void updateSettings(SQLiteDatabase db, Context context)
	{
		SettingsRepository settingsRepository = new SettingsRepository(context);
		Cursor cursor = null;
        try
        {
        	// perform query
        	cursor = db.rawQuery(SETTINGS_QUERY, null);
                
            // get over results
            if (cursor.moveToFirst())
            {
            	// get settings
            	settingsRepository.setUnitOfMeasure(UnitOfMeasure.valueOf(Database.getString(cursor, "unit_of_measure")));
            	settingsRepository.setExitAltitude(Database.getInteger(cursor, "default_exit_altitude"));
            	settingsRepository.setDeploymentAltitude(Database.getInteger(cursor, "default_deployment_altitude"));
            	settingsRepository.setFreefallTime(Database.getInteger(cursor, "freefall_time"));
            	settingsRepository.setCutaways(Database.getInteger(cursor, "cutaways"));
            }
        }
        finally
        {
            // close cursor
        	if (cursor != null)
        	{
        		cursor.close();
            }
        }
        
        db.execSQL("DROP TABLE logbook_history");
        db.execSQL("DROP TABLE settings");
	}
}